import datetime
import pymysql

# 数据库操作
config = {
    "host": "localhost",
    "user": "root",
    "password": "xuhuan.59",
    "database": "my_deicide",
    "charset": "utf8"
}

conn = pymysql.connect(**config)


# 根据日期来查询当日记录
def find_all():
    cursor = conn.cursor()
    # sql = "UPDATE %s SET user_no = '%s' WHERE user_id = '%s'" % (table_name, user_no, user_id)
    query = "select * from t_task_day"
    cursor.execute(query)
    result = cursor.fetchall()
    list_data = []

    for (item) in result:
        item_map = {}
        item_map['id'] = item[0]
        item_map['type'] = item[1]
        item_map['time'] = item[2]
        item_map['content'] = item[3]
        item_map['status'] = item[4]
        item_map['num'] = item[5]
        item_map['newtime'] = item[6]
        list_data.append(item_map)
    cursor.close()
    return list_data


# 根据日期来查询当日记录
def find_all_ing():
    cursor = conn.cursor()
    # sql = "UPDATE %s SET user_no = '%s' WHERE user_id = '%s'" % (table_name, user_no, user_id)
    query = "select * from t_task_day WHERE status = 0"
    cursor.execute(query)
    result = cursor.fetchall()
    list_data = []

    for (item) in result:
        item_map = {}
        item_map['id'] = item[0]
        item_map['type'] = item[1]
        item_map['time'] = item[2]
        item_map['content'] = item[3]
        item_map['status'] = item[4]
        item_map['num'] = item[5]
        item_map['newtime'] = item[6]
        list_data.append(item_map)
    cursor.close()
    return list_data


# 修改一条记录
def change_info(content, status, id):
    cursor = conn.cursor()
    sql = "UPDATE t_task_day SET content = '%s',status=%s WHERE id = '%s'" % (content, status, id)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


# 修改一条记录 修改成已完成
def change_info(id):
    cursor = conn.cursor()
    sql = "UPDATE t_task_day SET status=1 WHERE id = '%s'" % (id)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


# 修改一条记录  增加一次完成次数
def change_info_num(id, num):
    cursor = conn.cursor()
    try:
        print(num)
        dianz = 0
        if num is None:
            dianz = 1
        else:
            try:
                dianz = int(num) + 1
            except Exception as e1:
                print(e1)
                dianz = 1
        print(dianz)
        sql = "UPDATE t_task_day SET num= '%s',newtime= '%s' WHERE id = '%s'" % (dianz, datetime.datetime.now().strftime('%Y-%m-%d'), id)
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except Exception as e:
        print(e)
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


# 删除一条记录
def del_task_info(id):
    cursor = conn.cursor()
    sql = "DELETE FROM t_task_day WHERE id = '%s'" % (id)
    print(sql)
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


# 添加一条记录
def add_task(content):
    cursor = conn.cursor()
    sql = "INSERT INTO t_task_day (type, time, content, status,num,newtime) VALUES (%s,'%s','%s', %s, %s,'%s')" \
          % (0,
            datetime.datetime.now().strftime('%Y-%m-%d'),
            content,
            0,0,datetime.datetime.now().strftime('%Y-%m-%d'))
    try:
        # 执行sql语句
        cursor.execute(sql)
        # 提交到数据库执行
        conn.commit()
    except:
        # 如果发生错误则回滚
        conn.rollback()
    cursor.close()


if __name__ == '__main__':
    print(find_all(datetime.datetime.now().strftime('%Y-%m-%d')))
    # add_task('优化小鹿直播数据展示页面')
    # change_info('优化小鹿直播数据展示页面', 1, 5)
    conn.close()
